package com.fcscs.ecms.dao.hibernate;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.ws.schema.common.InfoDetailView;
import com.fcscs.cservice.ws.schema.common.InfoMasterView;
import com.fcscs.cservice.ws.schema.common.InfoMenuListView;
import com.fcscs.cservice.ws.schema.common.PictureFrameView;
import com.fcscs.cservice.ws.schema.common.PictureView;
import com.fcscs.ecms.dao.ECMSInfoDao;

@SuppressWarnings("unchecked")
@Repository("infoDao")
public class ECMSInfoDaoHibernate implements ECMSInfoDao {
	private static DBLoc TBLoc = DBLoc.GUEST;
	// @Autowired
	// private ECMSMainMenuManager ecmsMainMenuManager;
	@Autowired
	private ConnectionManager connMgr;

	@Override
	public InfoMasterView getInfoMasterView(long orgId, long propId,
			String langId, long id) {
		// TODO Auto-generated method stub
		String sql = "SELECT  ecms_hotel_staff_v03.global_module_info.id, "
				+ "(select ecms_hotel_staff_v03.global_text.textUnicodeValue "
				+ "from ecms_hotel_staff_v03.global_text "
				+ "where ecms_hotel_staff_v03.global_text.languageId = '"
				+ langId
				+ "' "
				+ "and ecms_hotel_staff_v03.global_text.id =ecms_hotel_staff_v03.global_module_info.titleRef) as 'Title', "
				+ " (select ecms_hotel_staff_v03.global_picture_frame.version "
				+ " from ecms_hotel_staff_v03.global_picture_frame "
				+ " where ecms_hotel_staff_v03.global_picture_frame.id =ecms_hotel_staff_v03.global_module_info.pictureFrameRef) as 'pictureFrameVersion' , "
				+ " ecms_hotel_staff_v03.global_module_info.pictureFrameRef, "
				+ " ecms_hotel_staff_v03.global_module_info.version,  ecms_hotel_staff_v03.mdl_info_main.itemId"
				+ " from ecms_hotel_staff_v03.mdl_info_main "
				+ "inner join ecms_hotel_staff_v03.global_module_info on ecms_hotel_staff_v03.global_module_info.id = ecms_hotel_staff_v03.mdl_info_main.id "
				+ "where ecms_hotel_staff_v03.global_module_info.proprietaryId = "
				+ propId + " and ecms_hotel_staff_v03.global_module_info.id = "
				+ id;
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql)
				.addScalar("id", Hibernate.INTEGER)
				.addScalar("Title", Hibernate.STRING)
				.addScalar("pictureFrameVersion", Hibernate.INTEGER)
				.addScalar("pictureFrameRef", Hibernate.INTEGER)
				.addScalar("version", Hibernate.INTEGER)
				.addScalar("itemId", Hibernate.INTEGER);
		List<Object[]> objects = new ArrayList<Object[]>();
		objects = sqlQu.list();
		InfoMasterView infoMasterView = new InfoMasterView();
		PictureFrameView pictureView = new PictureFrameView();
		List<Long> branchel = new ArrayList<Long>();
		for (Object[] objTmp : objects) {
			branchel.add(Long.parseLong(objTmp[5].toString()));
			pictureView.setId(Long.parseLong(objTmp[3].toString()));
			infoMasterView.setId(Long.parseLong(objTmp[0].toString()));
			infoMasterView.setVersion(Long.parseLong(objTmp[4].toString()));
			infoMasterView.setTitle(objTmp[1].toString());
		}
		// long frameId = Long.parseLong(objects.get(0)[3].toString());
		//
		// pictureView = ecmsMainMenuManager.getPictureFrameViews(orgId, propId,
		// frameId);
		// infoMasterView.setPictureFrameView(pictureView);
		infoMasterView.setPictureFrameView(pictureView);
		return infoMasterView;
	}

	@Override
	public InfoMasterView getInfoMasterView(long orgId, long propId,
			String langId) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Long> getBrachels(long parentId) {
		// TODO Auto-generated method stub
		System.out.println(parentId);
		String sql = "select ecms_hotel_staff_v03.mdl_info_item.id "
				+ "from ecms_hotel_staff_v03.mdl_info_item"
				+ " where ecms_hotel_staff_v03.mdl_info_item.parentId ="
				+ parentId;
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql)
				.addScalar("id", Hibernate.INTEGER);
		List<Object> objects = new ArrayList<Object>();
		objects = sqlQu.list();
		List<Long> idList = new ArrayList<Long>();
		if(objects.isEmpty()){
			return null;
		}
		
		for (Object objTmp : objects) {
			idList.add(Long.parseLong(objTmp.toString()));
		}
		return idList;
	}

	@Override
	public List<InfoMenuListView> getMaterInfoMenuListView(List<Long> idList,
			long orgId, long propId, String langId) {
		// TODO Auto-generated method stub
		String sql = "SELECT id , ifnull(parentId,-1) as 'parentId' , isBranch ,"
				+ "	 (select ecms_hotel_staff_v03.global_text.textUnicodeValue"
				+ "  from ecms_hotel_staff_v03.global_text"
				+ " where ecms_hotel_staff_v03.global_text.languageId = '"
				+ langId
				+ "'"
				+ "  and ecms_hotel_staff_v03.global_text.id =ecms_hotel_staff_v03.mdl_info_item.itemTitleRef) as 'Title',"
				+ " (select ecms_hotel_staff_v03.global_text.textUnicodeValue"
				+ "    from ecms_hotel_staff_v03.global_text"
				+ "    where languageId = '"
				+ langId
				+ "'"
				+ "   and id =ecms_hotel_staff_v03.mdl_info_item.menuTitleRef) as 'itemTitle',"
				+ "   (select ecms_hotel_staff_v03.global_text.textUnicodeValue"
				+ " from ecms_hotel_staff_v03.global_text"
				+ "    where languageId = '"
				+ langId
				+ "'"
				+ "      and id =ecms_hotel_staff_v03.mdl_info_item.menuSubTitleRef) as 'itemSubTitle',"
				+ "	 ecms_hotel_staff_v03.mdl_info_item.PictureFrameRef as 'pictureFrameId',"
				+ "      (select ecms_hotel_staff_v03.global_picture_frame.version"
				+ "  from ecms_hotel_staff_v03.global_picture_frame"
				+ "    where id =  ecms_hotel_staff_v03.mdl_info_item.pictureFrameRef) as 'pictureFrameVersion',"
				+ "     ecms_hotel_staff_v03.mdl_info_item.menuPictureRef as 'menuPictureId',"
				+ "  (select ecms_hotel_staff_v03.global_picture.version"
				+ "    from ecms_hotel_staff_v03.global_picture"
				+ "	 where id =ecms_hotel_staff_v03.mdl_info_item.menuPictureRef) as 'menuPictureRefVersion'"
				+ " FROM ecms_hotel_staff_v03.mdl_info_item" + "	where id  in(";
		StringBuffer appendBuffer = new StringBuffer();
		for (Long id : idList) {
			appendBuffer.append(id + ",");
		}
		appendBuffer.deleteCharAt(appendBuffer.length() - 1);

		sql = sql.concat(appendBuffer.toString() + ")");
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql)
				.addScalar("id", Hibernate.INTEGER)
				.addScalar("parentId", Hibernate.INTEGER)
				.addScalar("isBranch", Hibernate.BOOLEAN)
				.addScalar("Title", Hibernate.STRING)
				.addScalar("itemTitle", Hibernate.STRING)
				.addScalar("itemSubTitle", Hibernate.STRING)
				.addScalar("pictureFrameId", Hibernate.INTEGER)
				.addScalar("pictureFrameVersion", Hibernate.INTEGER)
				.addScalar("menuPictureId", Hibernate.INTEGER)
				.addScalar("menuPictureRefVersion", Hibernate.INTEGER);

		List<Object[]> objects = new ArrayList<Object[]>();
		objects = sqlQu.list();
		List<InfoMenuListView> menuListViews = new ArrayList<InfoMenuListView>();
		
		for (Object[] objTmp : objects) {
			
			InfoMenuListView menuListView = new InfoMenuListView();
			PictureFrameView pictureFrameView =  new PictureFrameView();
			PictureView pictureView = new PictureView();
			
			menuListView.setId(Long.parseLong(objTmp[0].toString()));
			menuListView.setParentId(Long.parseLong(objTmp[1].toString()));
			menuListView.setIsBranch(Boolean.parseBoolean(objTmp[2].toString()));
			pictureFrameView.setId(Long.parseLong(objTmp[6].toString()));
			pictureFrameView.setVersion(Long.parseLong(objTmp[7].toString()));
			pictureView.setId(Long.parseLong(objTmp[8].toString()));
			pictureView.setVersion(Long.parseLong(objTmp[9].toString()));
			menuListView.setTitle(objTmp[4].toString());
			menuListView.setSubtitle(objTmp[5].toString());
			menuListView.setTypeId("NULL");
			menuListView.setFrameTitle(objTmp[3].toString());
			menuListView.setPictureFrameView(pictureFrameView);
			menuListView.setPictureView(pictureView);
			menuListViews.add(menuListView);
		}
		return menuListViews;
	}

	@Override
	public InfoDetailView getInfoDetailView(long orgId, long propId ,  String langId ,long id ) {
		// TODO Auto-generated method stub
		String sql = "select ecms_hotel_staff_v03.mdl_info_item_detail.itemId," +
				" (select ecms_hotel_staff_v03.global_text.textUnicodeValue" +
				"   from ecms_hotel_staff_v03.global_text" +
				"	 where languageId = '"+langId+"'" +
				" and ecms_hotel_staff_v03.global_text.id =ecms_hotel_staff_v03.mdl_info_item_detail.descRef) as 'descript'," +
				"(select ecms_hotel_staff_v03.global_text.textUnicodeValue" +
				" from ecms_hotel_staff_v03.global_text" +
				" where languageId = '"+langId+"'" +
				" and ecms_hotel_staff_v03.global_text.id =ecms_hotel_staff_v03.mdl_info_item_detail.addressRef)" +
				" as 'address', ecms_hotel_staff_v03.mdl_info_item_detail.phone," +
				" ecms_hotel_staff_v03.mdl_info_item_detail.email,	ecms_hotel_staff_v03.mdl_info_item_detail.url " +
				"from ecms_hotel_staff_v03.mdl_info_item_detail where ecms_hotel_staff_v03.mdl_info_item_detail.itemId="+id;
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql)
					.addScalar("itemId",Hibernate.INTEGER)
					.addScalar("descript",Hibernate.STRING)
					.addScalar("address",Hibernate.STRING)
					.addScalar("phone",Hibernate.STRING)
					.addScalar("email",Hibernate.STRING)
					.addScalar("url",Hibernate.STRING);
		List<Object[]> objects = new ArrayList<Object[]>();
		objects = sqlQu.list();
		InfoDetailView infoDetailView = new InfoDetailView();
		for(Object[] objTmp : objects){
			infoDetailView.setId(Long.parseLong(objTmp[0].toString()));
			infoDetailView.setDescription(objTmp[1].toString());
			infoDetailView.setAddress(objTmp[2].toString());
			infoDetailView.setPhone(objTmp[3].toString());
			infoDetailView.setEmail(objTmp[4].toString());
			infoDetailView.setUrl(objTmp[5].toString());
		}
		return infoDetailView;
	}

}
